﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data;
using QuanLyBanHang.DataConnection;

namespace QuanLyBanHang.QuerySQL
{
    class ClassHoadonban
    {
        Connect conn = new Connect();
        public DataTable ShowHD()
        {
            DataTable tb = new DataTable(); ;
            try
            {
                string sql = "select MAHD,HOTEN,TENNV,NGAYLAP,TONGTIEN from HOADON,KHACHHANG,NHANVIEN where HOADON.MAKH = KHACHHANG.MAKH and HOADON.MANV = NHANVIEN.MANV";
                tb = conn.getTable(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
            return tb;
        }
        //trả về số lượng sản phẩm
        public double ShowSLSP(string masp)
        {
            double sl = 0;
            DataTable tb = new DataTable(); 

            try
            {
                string sql = "select SOLUONG from SANPHAM where MASP = '"+masp+"' ";
                tb = conn.getTable(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }

            foreach (DataRow row in tb.Rows)
            {
                sl = Convert.ToDouble(row["SOLUONG"].ToString());
            }
            return sl;
        }
        //
        public DataTable ShowCTHD()
        {
            DataTable tb = new DataTable(); ;
            try
            {
                string sql = "select *from CHITIET_HD";
                tb = conn.getTable(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
            return tb;
        }
        //
        public DataTable ShowMAHD(string mahd,string makh)
        {
            DataTable tb = new DataTable();
            try
            {
                string sql = "select MAHD,HOTEN,TENNV,NGAYLAP,TONGTIEN from HOADON,KHACHHANG,NHANVIEN where MAHD = '" + mahd + "' and HOADON.MAKH = '" + makh + "' and HOADON.MAKH = KHACHHANG.MAKH and HOADON.MANV = NHANVIEN.MANV ";
                tb = conn.getTable(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
            return tb;
        }
        //
        public DataTable TKCT(string mahd)
        {
            DataTable tb = new DataTable(); ;
            try
            {
                string sql = "select *from CHITIET_HD where MAHD = '" + mahd + "' ";
                tb = conn.getTable(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
            return tb;
        }
        //
        public DataTable Show_ChiTiet(string masp)
        {
            DataTable tb = new DataTable();
            try
            {
                string sql = "select TENSP, GIABAN from SANPHAM where MASP = '" + masp + "' ";
                tb = conn.getTable(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
            return tb;
        }
        //
        public DataTable ShowMASP()
        {
            DataTable tb = new DataTable();
            try
            {
                string sql = "select MASP from SANPHAM";
                tb = conn.getTable(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
            return tb;
        }
        //
        public DataTable ShowMAKH()
        {
            DataTable tb = new DataTable();
            try
            {
                string sql = "select MAKH from KHACHHANG";
                tb = conn.getTable(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
            return tb;
        }
        //tìm kiếm
        public DataTable TKHD(int i, string mahd = null, string tenkh = null)
        {
            DataTable dt = new DataTable();
            switch (i)
            {
                case 0:
                    {
                        try
                        {
                            String sql = "select MAHD,HOTEN,TENNV,NGAYLAP,TONGTIEN from HOADON,KHACHHANG,NHANVIEN where MAHD like '%" + mahd + "%' and HOADON.MAKH = KHACHHANG.MAKH and HOADON.MANV = NHANVIEN.MANV";
                            dt = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 1:
                    {
                        try
                        {
                            String sql = "select MAHD,HOTEN,TENNV,NGAYLAP,TONGTIEN from HOADON,KHACHHANG,NHANVIEN where HOTEN like N'%" + tenkh + "%' and HOADON.MAKH = KHACHHANG.MAKH and HOADON.MANV = NHANVIEN.MANV";
                            dt = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }
                case 2:
                    {
                        try
                        {
                            String sql = "select MAHD,HOTEN,TENNV,NGAYLAP,TONGTIEN from HOADON,KHACHHANG,NHANVIEN where MAHD like '%" + mahd + "%' and HOTEN like N'%" + tenkh + "%' and HOADON.MAKH = KHACHHANG.MAKH and HOADON.MANV = NHANVIEN.MANV";
                            dt = conn.getTable(sql);
                        }
                        catch (Exception e)
                        {
                            MessageBox.Show("Lỗi: " + e.Message);
                        }
                        break;
                    }                    
            }
            return dt;
        }
        // thêm vào bản hóa đơn
        public void ThemHD(string mahd, string makh, string manv)
        {
            try
            {
                string sql = "insert into HOADON(MAHD,MAKH,MANV) values('"+mahd+"','"+makh+"','"+manv+"')";
                conn.ExcuteQuerySql(sql);
                MessageBox.Show("Nhập chi tiết sản phẩm !!!");
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
        }
        //thêm vào bản chi tiết hd
        public void Them_CTHD(string mahd, string masp, string tensp, Double gia, Double slg, Double tong)
        {
            try
            {
                string sql = "insert into CHITIET_HD values('" + mahd + "','" + masp + "',N'"+tensp+"',"+gia+","+slg+","+tong+")";
                conn.ExcuteQuerySql(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
        }
        //
        public void SuaHD(string maHD, double tong)
        {
            try
            {
                string sql = "update HOADON set TONGTIEN = "+tong+" where MAHD = '"+maHD+"'";
                conn.ExcuteQuerySql(sql);
                MessageBox.Show("Thêm thành công !!!");
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
        }
        //
        public void SuaSLSP(string masp, double sl)
        {
            try
            {
                string sql = "update SANPHAM set SOLUONG = " + sl + " where MASP = '" + masp + "' ";
                conn.ExcuteQuerySql(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi : " + e.Message);
            }

        }
        // Xóa
        public void XoaHD(string mahd)
        {
            try
            {
                string sql = "delete HOADON where MAHD = '" + mahd + "'";
                conn.ExcuteQuerySql(sql);
                MessageBox.Show("Xóa thành công !!!");
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
        }
        public void XoaCTHD(string mahd)
        {
            try
            {
                string sql = "delete CHITIET_HD where MAHD = '" + mahd + "'";
                conn.ExcuteQuerySql(sql);
            }
            catch (Exception e)
            {
                MessageBox.Show("Lỗi: " + e.Message);
            }
        }
    }
}
